﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'proc_cm_MoveUpDatabaseContentByCd')
BEGIN
    PRINT 'Dropping Procedure proc_cm_MoveUpDatabaseContentByCd'
    DROP  Procedure  proc_cm_MoveUpDatabaseContentByCd
END
GO

PRINT 'Creating Procedure proc_cm_MoveUpDatabaseContentByCd'
GO

CREATE Procedure dbo.proc_cm_MoveUpDatabaseContentByCd
	@pContentCd varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

	declare @vTmp int, @vHtmlPageId uniqueidentifier, @vTmpId int
	declare @vTmp2 int, @vTmpId2 int

	select @vTmp = priority_num, @vHtmlPageId = html_page_id, @vTmpId = content_id
	from tbl_v2_Content
	where content_cd = @pContentCd

	select top 1 @vTmp2 = priority_num, @vTmpId2 = content_id
	from tbl_v2_Content
	where html_page_id = @vHtmlPageId
		and priority_num < @vTmp
	order by priority_num desc

	if @vTmpId2 is not null
	begin
		update tbl_v2_Content
		set priority_num = @vTmp2
		where content_id = @vTmpId

		update tbl_v2_Content
		set priority_num = @vTmp
		where content_id = @vTmpId2
	end
END
GO

GRANT EXEC ON dbo.proc_cm_MoveUpDatabaseContentByCd TO PUBLIC
GO


